﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using DBUtility;
using Model;
using IDAL;

namespace DAL
{
    public class ProductType_AgingInfo:IProductType_AgingInfo
    {
        #region 静态变量定义
        private const string PARM_AGING_PRODUCTTYPEID = "@ProductTypeID";
        private const string PARM_AGING_PRODUCTTYPENAME = "@ProductTypeName";
        private const string PARM_AGING_TIMESPEC = "@TimeSpec";
        private const string PARM_AGING_TEMPSPEC = "@TempSpec";
        private const string PARM_AGING_AGINGSTEP = "@AgingStep";
        private const string PARM_AGING_ATTRIBUTES = "@Attributes";
        private const string PARM_AGING_DEPARTMENT = "@Department";
        #endregion

        #region 信息维护部分
        public bool Insert(ProductType_AgingInfoInfo ai)
        {
            try
            {
                SqlParameter[] testParms = GetParameters();
                //Bind the parameter
                testParms[0].Value = ai.ProductTypeID;
                testParms[1].Value = ai.ProductTypeName;
                testParms[2].Value = ai.TimeSpec;
                testParms[3].Value = ai.TempSpec;
                testParms[4].Value = ai.AgingStep;
                testParms[5].Value = ai.Attributes;
                testParms[6].Value = ai.Department;
                SqlCommand cmd = new SqlCommand();
                foreach (SqlParameter parm in testParms)
                    cmd.Parameters.Add(parm);
                using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "ProductType_AgingInfo_Insert";
                    using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
                }
                cmd.Parameters.Clear();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool Update(ProductType_AgingInfoInfo ai)
        {
            try
            {
                SqlParameter[] testParms = GetParameters();
                //Bind the parameter
                testParms[0].Value = ai.ProductTypeID;
                testParms[1].Value = ai.ProductTypeName;
                testParms[2].Value = ai.TimeSpec;
                testParms[3].Value = ai.TempSpec;
                testParms[4].Value = ai.AgingStep;
                testParms[5].Value = ai.Attributes;
                testParms[6].Value = ai.Department;
                SqlCommand cmd = new SqlCommand();
                foreach (SqlParameter parm in testParms)
                    cmd.Parameters.Add(parm);
                using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "ProductType_AgingInfo_Update";
                    using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
                }
                cmd.Parameters.Clear();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool Delete(string productTypeId,string agingStep)
        {
            try
            {
                //Create a parameter    
                SqlParameter[] testParms = Get2Parameters();
                //Bind the parameter
                testParms[0].Value = productTypeId;
                testParms[1].Value = agingStep;
                SqlCommand cmd = new SqlCommand();
                foreach (SqlParameter parm in testParms)
                    cmd.Parameters.Add(parm);
                using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "ProductType_AgingInfo_Delete";
                    cmd.ExecuteNonQuery();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 参数部分
        private static SqlParameter[] Get2Parameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
					new SqlParameter(PARM_AGING_PRODUCTTYPEID, SqlDbType.NVarChar, 50),
                    new SqlParameter(PARM_AGING_AGINGSTEP, SqlDbType.NVarChar, 10),             
                };
            return parms;
        }

        private static SqlParameter[] GetParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
					new SqlParameter(PARM_AGING_PRODUCTTYPEID, SqlDbType.NVarChar, 50),
                    new SqlParameter(PARM_AGING_PRODUCTTYPENAME, SqlDbType.NVarChar, 50),
					new SqlParameter(PARM_AGING_TIMESPEC, SqlDbType.Decimal, 4),
                    new SqlParameter(PARM_AGING_TEMPSPEC, SqlDbType.Int, 4),
                    new SqlParameter(PARM_AGING_AGINGSTEP, SqlDbType.NVarChar, 10),
					new SqlParameter(PARM_AGING_ATTRIBUTES, SqlDbType.NVarChar, 10),
                    new SqlParameter(PARM_AGING_DEPARTMENT, SqlDbType.NVarChar, 10),              
                };
            return parms;
        }
        #endregion

        #region 数据检索部分
        //-------------------------上面数据操作，下面数据检索----------------------------------------------------------------------
        public IList<ProductType_AgingInfoInfo> GetPAIs()
        {
            IList<ProductType_AgingInfoInfo> pais = new List<ProductType_AgingInfoInfo>();

            //Execute a query to read the categories
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ProductType_AgingInfo_select", null))
            {
                while (rdr.Read())
                {
                    ProductType_AgingInfoInfo pai = new ProductType_AgingInfoInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetDecimal(2), rdr.GetInt32(3), rdr.GetValue(4).ToString(), rdr.GetValue(5).ToString(), rdr.GetValue(6).ToString());
                    pais.Add(pai);
                }
            }
            return pais;
        }

        public IList<ProductType_AgingInfoInfo> GetPAIsbyPID(string productTypeID)
        {
            IList<ProductType_AgingInfoInfo> pais = new List<ProductType_AgingInfoInfo>();
            SqlParameter parm = new SqlParameter(PARM_AGING_PRODUCTTYPEID, SqlDbType.NVarChar, 50);
            parm.Value = productTypeID;

            //Execute a query to read the categories
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ProductType_AgingInfo_selectbyPID", parm))
            {
                while (rdr.Read())
                {
                    ProductType_AgingInfoInfo pai = new ProductType_AgingInfoInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetDecimal(2), rdr.GetInt32(3), rdr.GetValue(4).ToString(), rdr.GetValue(5).ToString(), rdr.GetValue(6).ToString());
                    pais.Add(pai);
                }
            }
            return pais;
        }

        public ProductType_AgingInfoInfo GetPAI(string productTypeId,string agingStep)
        {
            ProductType_AgingInfoInfo pai = null;

            //Create a parameter    
            SqlParameter[] testParms = Get2Parameters();
            //Bind the parameter
            testParms[0].Value = productTypeId;
            testParms[1].Value = agingStep;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ProductType_AgingInfo_selectbyID", testParms))
            {
                if (rdr.Read())

                    pai = new ProductType_AgingInfoInfo(rdr.GetValue(0).ToString(), rdr.GetDecimal(1), rdr.GetInt32(2), rdr.GetValue(3).ToString());
                else
                    pai = new ProductType_AgingInfoInfo();
            }
            return pai;
        }
        #endregion
    }
}
